package com.cntcdt.api.db.dao;

import java.text.SimpleDateFormat;
import java.util.List;

import com.cntcdt.api.db.dao.base.GroupDeviceDayDivideCntDemoDaoBase;
import com.cntcdt.api.db.model.GroupDeviceDayDivideCntDemoModel;
import com.cntcdt.api.db.model.GroupDeviceDayDivideCntModel;
import com.ibm.icu.util.Calendar;

/**
 * 这是cdt_group_device_day_divide_cnt_demo的Dao实现类
 */
public class GroupDeviceDayDivideCntDemoDao extends GroupDeviceDayDivideCntDemoDaoBase {
	/**
	 * GroupDeviceDayDivideCntDemoDao对象
	 */
	private static GroupDeviceDayDivideCntDemoDao instance = new GroupDeviceDayDivideCntDemoDao();

	/**
	 * 默认构造方法
	 */
	private GroupDeviceDayDivideCntDemoDao() {
	}

	/**
	 * 获得对象实例
	 */
	public static GroupDeviceDayDivideCntDemoDao getInstance() {
		return instance;
	}

	/**
	 * 按昨天的集团数据
	 * 
	 * @param id
	 *            id
	 * @return model
	 */
	public GroupDeviceDayDivideCntDemoModel findByYesterday(int groupUserId) {
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DATE, -1);
		String yestedayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE + " WHERE addTime = ? and groupUserId = ? order by addTime";
		Object[] paramObjs = { yestedayDate, groupUserId };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		if (modelList.size() > 0) {
			return modelList.get(0);
		}
		return null;
	}

	/**
	 * 查出本月的集团数据
	 * 
	 * @param id
	 *            id
	 * @return model
	 */
	public GroupDeviceDayDivideCntDemoModel findByThisMonth(int groupUserId) {
		Calendar cal_2 = Calendar.getInstance();
		cal_2.add(Calendar.MONTH, 0);
		cal_2.set(Calendar.DAY_OF_MONTH, 1);// 设置为1号,当前日期既为本月第一天
		String firstDay = new SimpleDateFormat("yyyy-MM-dd").format(cal_2.getTime());

		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DATE, 0);
		String todayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE + " WHERE addTime >= ? and addTime <= ? and groupUserId = ? order by addTime";
		Object[] paramObjs = { firstDay, todayDate, groupUserId };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		if (modelList.size() > 0) {
			return modelList.get(0);
		}
		return null;
	}

	/**
	 * 查出近7天的集团数据
	 * 
	 * @param id
	 *            id
	 * @return model
	 */
	public List<GroupDeviceDayDivideCntDemoModel> findByLastSevenDayData(int groupUserId) {
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DATE, -1);
		String yesterdayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());

		Calendar calendar2 = Calendar.getInstance();
		calendar2.add(Calendar.DATE, -7);
		String lastSevenDayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar2.getTime());

		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,SUM(borrowRentSum) as borrowRentSum,SUM(sellLineMoneySum) as sellLineMoneySum,SUM(sellCDBSum) as sellCDBSum,sellCDBNum,sellLineNum,presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE + " WHERE addTime >= ? and addTime <= ? and groupUserId = ? GROUP BY addTime";
		Object[] paramObjs = { lastSevenDayDate, yesterdayDate, groupUserId };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}

	/**
	 * 按昨天的集团数据
	 * 
	 * @param id
	 *            id
	 * @return model
	 */
	public GroupDeviceDayDivideCntDemoModel findByYesterday(int groupUserId, String terminal) {
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DATE, -1);
		String yestedayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE + " WHERE addTime = ? and groupUserId = ? and terminal = ? order by addTime";
		Object[] paramObjs = { yestedayDate, groupUserId, terminal };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		if (modelList.size() > 0) {
			return modelList.get(0);
		}
		return null;
	}

	/**
	 * 查出本月的集团数据
	 * 
	 * @param id
	 *            id
	 * @return model
	 */
	public GroupDeviceDayDivideCntDemoModel findByThisMonth(int groupUserId, String terminal) {
		Calendar cal_2 = Calendar.getInstance();
		cal_2.add(Calendar.MONTH, 0);
		cal_2.set(Calendar.DAY_OF_MONTH, 1);// 设置为1号,当前日期既为本月第一天
		String firstDay = new SimpleDateFormat("yyyy-MM-dd").format(cal_2.getTime());

		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DATE, 0);
		String todayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE + " WHERE addTime >= ? and addTime <= ? and groupUserId = ? and terminal = ? order by addTime";
		Object[] paramObjs = { firstDay, todayDate, groupUserId, terminal };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		if (modelList.size() > 0) {
			return modelList.get(0);
		}
		return null;
	}

	/**
	 * 查出近7天的集团数据
	 * 
	 * @param id
	 *            id
	 * @return model
	 */
	public List<GroupDeviceDayDivideCntDemoModel> findByLastSevenDayData(int groupUserId, String terminal) {
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DATE, -1);
		String yesterdayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());

		Calendar calendar2 = Calendar.getInstance();
		calendar2.add(Calendar.DATE, -7);
		String lastSevenDayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar2.getTime());

		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,SUM(borrowRentSum) as borrowRentSum,SUM(sellLineMoneySum) as sellLineMoneySum,SUM(sellCDBSum) as sellCDBSum,sellCDBNum,sellLineNum,presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE + " WHERE addTime >= ? and addTime <= ? and groupUserId = ? and terminal = ? GROUP BY addTime";
		Object[] paramObjs = { lastSevenDayDate, yesterdayDate, groupUserId, terminal };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}

	/**
	 * 查出本月的集团数据
	 * 
	 * @param id
	 *            id
	 * @return model
	 */

	public List<GroupDeviceDayDivideCntDemoModel> findBySortTimeSum(int groupUserId, long sortTime) {
		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE + " WHERE groupUserId = ? and sortTime < ? GROUP BY addTime order by sortTime desc";
		Object[] paramObjs = { groupUserId, sortTime };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}

	/**
	 * 按昨天的集团数据
	 * 
	 * @param id
	 *            id
	 * @return model
	 */
	public List<GroupDeviceDayDivideCntDemoModel> findMonthDataBySortTime(int groupUserId, long sortTime) {
		String sql = "select id,terminal,name,address,addTime,updateTime,sortTime,DATE_FORMAT(addTime,'%Y%m') months,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney from "
				+ TABLE + " where groupUserId = ? and sortTime < ? group by months order by months desc limit 20";
		Object[] paramObjs = { groupUserId, sortTime };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}

	/**
	 * 查出本月的集团数据
	 * 
	 * @param id
	 *            id
	 * @return model
	 */
	public List<GroupDeviceDayDivideCntDemoModel> findBySortTimeSum(int groupUserId, long sortTime, String terminal) {
		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE
				+ " WHERE groupUserId = ? and sortTime < ? and terminal = ? GROUP BY addTime order by sortTime desc limit 20";
		Object[] paramObjs = { groupUserId, sortTime, terminal };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}

	/**
	 * 按昨天的集团数据
	 * 
	 * @param id
	 *            id
	 * @return model
	 */
	public List<GroupDeviceDayDivideCntDemoModel> findMonthDataBySortTime(int groupUserId, long sortTime,
			String terminal) {
		String sql = "select id,terminal,name,address,addTime,updateTime,sortTime,DATE_FORMAT(addTime,'%Y%m') months,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney from "
				+ TABLE
				+ " where groupUserId = ? and sortTime < ? and terminal = ? group by months order by months desc limit 20";
		Object[] paramObjs = { groupUserId, sortTime, terminal };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}

	public List<GroupDeviceDayDivideCntDemoModel> findByDateTime(int groupUserId, String time) {
		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE + " WHERE groupUserId = ? and addTime = ? GROUP BY terminal order by sortTime desc";
		Object[] paramObjs = { groupUserId, time };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}

	public List<GroupDeviceDayDivideCntDemoModel> findByDate(int groupUserId, String startDate, String endDate) {
		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE
				+ " WHERE groupUserId = ? and addTime >=? and addTime<= ? GROUP BY addTime order by sortTime desc";
		Object[] paramObjs = { groupUserId, startDate, endDate };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}

	public List<GroupDeviceDayDivideCntDemoModel> findSingleDeviceByDate(int groupUserId, String terminal,
			String startDate, String endDate) {
		String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,groupUserId,higherUpGroupUserId,residueBorrowRentSum,residueSellLineMoneySum,residueSellCDBSum,level,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney,residueBuyCDBGiveBackMoney FROM "
				+ TABLE
				+ " WHERE groupUserId = ? and terminal = ? and addTime >=? and addTime<= ? GROUP BY addTime order by sortTime desc";
		Object[] paramObjs = { groupUserId, terminal, startDate, endDate };
		List<GroupDeviceDayDivideCntDemoModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}
}
